﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace excelReadSave
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        /// <summary>
        /// 读取 excel 表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_read_Click(object sender, EventArgs e)
        {
            OpenFileDialog fileDialog = new OpenFileDialog();
            fileDialog.Multiselect = true;
            fileDialog.Title = "请选择文件";
            fileDialog.Filter = "所有文件(*xlsx*)|*.xlsx*|所有文件(*xls*)|*.xls*"; //设置要选择的文件的类型
            fileDialog.ShowDialog();
            string path = fileDialog.FileName;

            var exceldata = this.GetExcelConfig(path,0);
            foreach (var item in exceldata)
            {

            }
        }

        /// <summary>
        /// 保存excel 表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_save_Click(object sender, EventArgs e)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "curExcel(*.xlsx)|*.xlsx|curExcel(*.xls)|*.xls";
            saveFileDialog.ShowDialog();
            string path = saveFileDialog.FileName;

            this.SaveExcel(path);
        }

        //保存excel
        #region //保存excel

        /// <summary>
        /// 保存 excel 
        /// </summary>
        /// <param name="savePath"></param>
        private void SaveExcel(string savePath)
        {

            IWorkbook workbook = null;
            if (savePath.LastIndexOf(".xlsx") > 0)  // 2007
            {
                workbook = new XSSFWorkbook();   // 2003
            }
            if (savePath.LastIndexOf(".xls") > 0)
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                workbook = new HSSFWorkbook();
            }
            //如果有多页则填写多个
            this.CreatTempSheet(ref workbook);
            this.SaveExcelToPath(workbook, savePath);
        }


        /// <summary>
        /// 创建一页临时数据
        /// </summary>
        /// <param name="workboo"></param>
        private void CreatTempSheet(ref IWorkbook workboo)
        {
            // 表头数据 
            List<List<string>> header = new List<List<string>>();
            // 每一行数据
            List<string> list1 = new List<string>();
            list1.Add("头标识1");
            list1.Add("头标识2");
            header.Add(list1);

            //表内容数据
            List<List<string>> datas = new List<List<string>>();
            List<string> data1 = new List<string>();
            data1.Add("第一行01");
            data1.Add("第一行02");
            datas.Add(data1);

            List<string> data2 = new List<string>();
            data2.Add("第二行01");
            data2.Add("第二行02");
            datas.Add(data2);

            this.CreatOneSheet(header, datas,"temp",ref workboo);

        }


        /// <summary>
        /// 创建 excel 页
        /// </summary>
        /// <param name="headers">表头</param>
        /// <param name="datas">表数据</param>
        /// <param name="sheetName">页面名称</param>
        /// <param name="workbook"></param>
        private void CreatOneSheet(List<List<string>> headers, List<List<string>> datas, string sheetName, ref IWorkbook workbook)
        {
            ISheet sheet;  
            var appendRow = 0;  

            sheet = workbook.CreateSheet(sheetName);  
            sheet.DefaultColumnWidth = 40;

            datas.InsertRange(0, headers);

            foreach (List<string> values in datas)
            {
                var row = sheet.CreateRow(appendRow);
                var cellnum = 0;  
                foreach (string value in values)
                {
                    var cell = row.CreateCell(cellnum);
                    cell.SetCellValue(value);
                    cellnum++;
                }
                appendRow++;
            }
        }


        /// <summary>
        /// 保存流文件（excel）
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="savePath"></param>
        private void SaveExcelToPath(IWorkbook workbook, string savePath)
        {
            FileStream fs = null;
            try
            {
                fs = new FileStream(savePath, FileMode.OpenOrCreate);
                if (fs.CanWrite)
                {
                    workbook.Write(fs);
                    workbook.Close();
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                    fs.Dispose();
                }

            }
            MessageBox.Show("保存成功");
        }

        #endregion

        /// <summary>
        /// 读取 excel 
        /// </summary>
        /// <param name="openPath"></param>
        /// <param name="sheetIdx"></param>
        /// <returns></returns>
        private List<List<string>> GetExcelConfig(string openPath, int sheetIdx)
        {
            List<List<string>> datas = new List<List<string>>();

            if (!File.Exists(openPath))
            {
                return datas;
            }
            try
            {
                using (var fs = new FileStream(openPath, FileMode.Open))
                {
                    IWorkbook workbook = null;
                    // 2007版本  
                    if (openPath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (openPath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);
                    else
                        workbook = new HSSFWorkbook(fs);

                    if (workbook.NumberOfSheets > 0)
                    {
                        var sheet = workbook.GetSheetAt(sheetIdx);
                        if (sheet != null && sheet.LastRowNum > 0)
                        {
                            ICell cell = null;
                            IRow firstRow = sheet.GetRow(0); //取第几行
                            int cellCount = firstRow.LastCellNum;//列数(以某一行最大数作为列数)
                            //遍历每一行数据
                            for (var rownum = 0; rownum <= sheet.LastRowNum; rownum++)
                            {
                                try
                                {
                                    string _val_ = "";
                                    var row = sheet.GetRow(rownum);
                                    if (row == null)
                                    {
                                        continue;
                                    }
                                    //空行或空格认为是空的字符数据
                                    cell = row.GetCell(0);
                                    if (cell == null)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        _val_ = cell.StringCellValue;
                                        if (string.IsNullOrEmpty(_val_))
                                        {
                                            continue;
                                        }
                                    }
                                    var data = new List<string>();
                                    for (int j = 0; j < cellCount; ++j)
                                    {
                                        try
                                        {
                                            cell = row.GetCell(j);
                                            if (cell == null)
                                            {
                                                _val_ = "";
                                            }
                                            else
                                            {
                                                _val_ = "";
                                                try
                                                {
                                                    switch (cell.CellType)
                                                    {
                                                        case CellType.Numeric:
                                                            _val_ = cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
                                                            break;

                                                        case CellType.Boolean:
                                                            _val_ = cell.BooleanCellValue.ToString(CultureInfo.InvariantCulture);
                                                            break;

                                                        case CellType.String:
                                                            _val_ = cell.StringCellValue;
                                                            break;

                                                        default:
                                                            _val_ = cell.StringCellValue;
                                                            break;
                                                    }
                                                }
                                                catch (Exception e)
                                                {
                                                    _val_ = "";
                                                    MessageBox.Show(e.ToString());
                                                }
                                            }
                                            data.Add(_val_);

                                        }
                                        catch (Exception e)
                                        {

                                            MessageBox.Show(e.ToString());
                                            return null;
                                        }
                                    }
                                    datas.Add(data);
                                }
                                catch (Exception e)
                                {
                                    MessageBox.Show(e.ToString());
                                    return null;
                                }
                            }
                        }
                    }
                    fs.Close();
                    fs.Dispose();
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                throw;
            }
            return datas;
        }
    }
}
